from pydive.model import DeclarativeBase, metadata, DBSession
from sqlalchemy import Table, ForeignKey, Column, SmallInteger, Float, Sequence
from sqlalchemy.orm import mapper, relation, relation, backref
from sqlalchemy.types import Integer, Unicode, DateTime
from pydive.model.auth import User
   
class Difficulty(DeclarativeBase):
    __tablename__ = 'Difficulty'
     
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(50))
    rating = Column(SmallInteger)
  
class GlobalLightIntensity(DeclarativeBase):
    __tablename__ = 'GlobalLightIntensity'
     
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(Unicode(50))
    value = Column(SmallInteger)
class Address(DeclarativeBase):
    __tablename__ = 'Address'

    id = Column(Integer, primary_key=True,autoincrement=True)
    street = Column(Unicode(120))
    city = Column(Unicode(120))
    postcode = Column(Unicode(120))
    country = Column(Unicode(120))
    province = Column(Unicode(120)) 

class Contact(DeclarativeBase):
    __tablename__ = 'Contact'
    
    id = Column(Integer, primary_key=True,autoincrement=True)
    language = Column(Unicode(20))
    phone = Column(Unicode(20))
    mobilephone = Column(Unicode(20))
    fax = Column(Unicode(20))
    email = Column(Unicode(40))
    homepage =  Column(Unicode(40))

class DiveSite(DeclarativeBase):
    '''http://www.streit.cc/extern/uddf_v220en/ch09s74.html'''
    __tablename__ = 'DiveSite'

    id = Column(Integer, primary_key=True,autoincrement=True)
    name = Column(Unicode(120), nullable=False)
    location = Column(Unicode(120))
    latitude = Column(Float ,index=True)
    longitude = Column(Float ,index=True)
    altitude =  Column(Float)
    timezone = Column(SmallInteger)
    arealength = Column(Float)
    areawidth = Column(Float)
    averagevisibility = Column(Float)
    bottom = Column(Unicode(120))
    density = Column(Float) 
    difficulty_id = Column(Integer,ForeignKey('Difficulty.id'))
    difficulty = relation(Difficulty)
    globallightintensity_id = Column(Integer,ForeignKey('GlobalLightIntensity.id')) 
    globallightintensity = relation(GlobalLightIntensity)
    maximumdepth = Column(Float)
    maximumvisibility = Column(Float)
    minimumdepth = Column(Float)
    minimumvisibility = Column(Float)
    #REMOVE VER 8
    #rating = Column(SmallInteger)
    terrain = Column(Unicode(250))
    # address table
    address_id = Column(Integer,ForeignKey('Address.id'))
    address = relation(Address)
    # ADDED VER 8
    creator_id = Column(Integer ,ForeignKey('tg_user.user_id'))
    creator = relation(User, backref='diveSites')
    
    
  
    
    #shape
    #connect to features cave, wreck, indoor, lake ...
    #connect to address table
    #ecology
    #wiki link 
    #notes (can contain images text video) maby a wiki style
    #global personal work in progress  points for creator
    #designated moderators 
    #versions

# ADDED VER 8    
class DiveSiteComment(DeclarativeBase):
    __tablename__ = 'DiveSiteComment'
    diveSite_id = Column(Integer ,ForeignKey('DiveSite.id'))
    diveSite = relation(DiveSite, backref='comments')
    id = Column(Integer, primary_key=True,autoincrement=True)
    rating = Column(SmallInteger)
    text = Column(Unicode(250))
    user_id = Column(Integer ,ForeignKey('tg_user.user_id'))
    user = relation(User, backref='diveSiteComments')
    
    
class Diver(DeclarativeBase):
    __tablename__ = 'Diver'
    id = Column(Integer, primary_key=True,autoincrement=True)
    firstname = Column(Unicode(120))
    middlename = Column(Unicode(120))
    lastname = Column(Unicode(120))
    nickname = Column(Unicode(120))     #dbver 10
    image = Column(Unicode(120))        #dbver 11
    title = Column(Unicode(120))
    sex = Column(SmallInteger)
    birthdate = Column(DateTime)
    passport = Column(Unicode(120))
    address_id = Column(Integer,ForeignKey('Address.id'))
    address = relation(Address)
    contact_id = Column(Integer,ForeignKey('Contact.id'))
    contact = relation(Contact)
    user_id = Column(Integer, ForeignKey('tg_user.user_id'))
    user = relation(User, uselist=False, single_parent=True, backref='diver')
    


dive_diver = Table('dive_diver', metadata,
     Column('dive_id', Integer, ForeignKey('Dive.id')),
     Column('diver_id', Integer, ForeignKey('Diver.id'))
)


class Dive(DeclarativeBase):
    __tablename__ = 'Dive'
    #equipment v0.4
    #trip membership v0.4
    id = Column(Integer, primary_key=True,autoincrement=True)
    date = Column(DateTime ,index=True)
    duration = Column(SmallInteger)
    altitude = Column(Float)
    airtemperature = Column(Float)
    lowesttemperature = Column(Float) 
    greatestdepth = Column(Float)
    density  = Column(Float) 
    diver_id = Column(Integer ,ForeignKey('Diver.id'),index=True)
    diver = relation(Diver, backref='dives')
    buddies = relation('Diver', secondary=dive_diver, backref='divesAsBuddy')
    notes = Column(Unicode(255))
    diveSite_id = Column(Integer ,ForeignKey('DiveSite.id'))
    diveSite = relation(DiveSite, backref='dives')
    
    